using System;
using System.Data;
using System.Data.OleDb;
using System.Text;
using PCSComUtils.Common;
using PCSComUtils.DataAccess;

using PCSComUtils.PCSExc;

namespace PCSComMaterials.Inventory.DS
{
	public class IV_BalanceBinDS
	{
		private const string THIS = "PCSComMaterials.Inventory.DS.DS.IV_BalanceBinDS";

		public DataSet List(DateTime pdtmEffectDate)
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();


			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT "
					+ IV_BalanceBinTable.BALANCEBINID_FLD + ","
					+ IV_BalanceBinTable.EFFECTDATE_FLD + ","
					+ IV_BalanceBinTable.OHQUANTITY_FLD + ","
					+ IV_BalanceBinTable.COMMITQUANTITY_FLD + ","
					+ IV_BalanceBinTable.PRODUCTID_FLD + ","
					+ IV_BalanceBinTable.LOCATIONID_FLD + ","
					+ IV_BalanceBinTable.BINID_FLD + ","
					+ IV_BalanceBinTable.STOCKUMID_FLD
					+ " FROM " + IV_BalanceBinTable.TABLE_NAME
					+ " WHERE DATEPART(year, EffectDate) = " + pdtmEffectDate.Year
					+ " AND DATEPART(month, EffectDate) = " + pdtmEffectDate.Month;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, IV_BalanceBinTable.TABLE_NAME);

				return dstPCS;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}


		}
		/// <summary>
		/// Get all OnHand Balance Bin in this period and previous period
		/// </summary>
		/// <param name="pdtmEffectDate"></param>
		/// <returns></returns>
		/// <author>Trada</author>
		/// <date>Wednesday, October 11 2006</date>
		public DataSet GetAllOnHandBalanceBinInTwoPeriod(DateTime pdtmEffectDate)
		{
			const string METHOD_NAME = THIS + ".GetAllOnHandBalanceBinInTwoPeriod()";
			DataSet dstPCS = new DataSet();


			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;

				strSql = "SELECT "
					+ IV_BalanceBinTable.BALANCEBINID_FLD + ","
					+ IV_BalanceBinTable.EFFECTDATE_FLD + ","
					+ IV_BalanceBinTable.OHQUANTITY_FLD + ","
					+ IV_BalanceBinTable.COMMITQUANTITY_FLD + ","
					+ IV_BalanceBinTable.PRODUCTID_FLD + ","
					+ IV_BalanceBinTable.LOCATIONID_FLD + ","
					+ IV_BalanceBinTable.BINID_FLD + ","
					+ IV_BalanceBinTable.STOCKUMID_FLD
					+ " FROM " + IV_BalanceBinTable.TABLE_NAME
					+ " WHERE " + IV_BalanceBinTable.EFFECTDATE_FLD + " = '" + pdtmEffectDate.ToShortDateString() + "'"
					+ " OR " + IV_BalanceBinTable.EFFECTDATE_FLD + " = '" + pdtmEffectDate.AddMonths(-1).ToShortDateString() + "'";
				
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, IV_BalanceBinTable.TABLE_NAME);

				return dstPCS;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}


		}

		public DataTable GetBalanceQuantity(DateTime pdtmDate)
		{
			const string METHOD_NAME = THIS + ".GetBalanceQuantity()";
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				DateTime dtmLastMonth = pdtmDate.AddMonths(-1);
				string strSql = "SELECT ProductID, SUM(ISNULL(OHQuantity,0)) AS OHQuantity"
					+ " FROM IV_BalanceBin IB JOIN MST_BIN MB ON IB.BinID = MB.BinID"
					+ " AND MB.BinTypeID <> " + (int)BinTypeEnum.LS
					+ " WHERE DATEPART(year, EffectDate) = " + dtmLastMonth.Year
					+ " AND DATEPART(month, EffectDate) = " + dtmLastMonth.Month
					+ " GROUP BY ProductID ORDER BY ProductID";
				
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				DataTable dtbData = new DataTable();
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dtbData);
				return dtbData;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public DataTable GetAvailableQuantityForPlan(DateTime pdtmDate)
		{
			const string METHOD_NAME = THIS + ".GetAvailableQuantityForPlan()";
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				DateTime dtmLastMonth = pdtmDate.AddMonths(-1);
				string strSql = "SELECT IB.LocationID, ProductID, SUM(ISNULL(OHQuantity,0)) AS SupplyQuantity"
					+ " FROM IV_BalanceBin IB JOIN MST_BIN MB ON IB.BinID = MB.BinID"
					+ " AND MB.BinTypeID NOT IN (" + (int)BinTypeEnum.NG + "," + (int)BinTypeEnum.LS + ")"
					+ " WHERE DATEPART(year, EffectDate) = " + dtmLastMonth.Year
					+ " AND DATEPART(month, EffectDate) = " + dtmLastMonth.Month
					+ " GROUP BY IB.LocationID, ProductID ORDER BY IB.LocationID, ProductID";
				
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				DataTable dtbData = new DataTable();
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dtbData);
				return dtbData;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}
	}
}